package com.zzzzzz.content.article;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.content.Constants;

@Repository
public class ArticleDao {
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	private static final String ADD = 				"INSERT INTO topic(title, text_s, text_n, tags, user_id, add_dt, upd_dt, status, type_id, looks, replies, likes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String EDIT_STATUS = 		"update topic set status = ?, upd_dt = ? where id = ? and user_id = ?";
	private static final String EDIT = 				"update topic set title = ?, text_s = ?, text_n = ?, tags = ?, upd_dt = ? where id = ? and user_id = ?";
	
	private static final String FIND_BY_ID = 		"select t.id, t.title, t.text_s, t.text_n, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.looks, t.replies, t.likes from topic t where t.id = ?";
	private static final String FIND_VIEW_BY_ID = 	"select t.id, t.title, t.text_s, t.text_n, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.looks, t.replies, t.likes, u.username, ifnull(u.website, u.id) as website from topic t inner join user u on t.user_id = u.id where t.id = ?";
	
	private final static String EDIT_LOOKS = 		"update topic set looks = looks + ? where id = ?";
	private static final String EDIT_REPLIES = 		"update topic set replies = replies + ? where id = ?";
	private final static String EDIT_LIKES = 		"update topic set likes = likes + ? where id = ?";
	
	public void add(Article article) {
		jdbcTemplate.update(ADD, article.getTitle(), article.getTextS(), article.getTextN(), article.getTags(), article.getUserId(), new Date(), new Date(), article.getStatus(), Constants.CONTENT_TYPE.ARTICLE, article.getLooks(), article.getReplies(), article.getLikes());
		Long id = jdbcTemplate.queryForLong("SELECT @@IDENTITY");
		article.setId(id);
	}

	public void editStatus(Long id, Integer status, Long userId) {
		jdbcTemplate.update(EDIT_STATUS, status, new Date(), id, userId);
	}

	public void edit(Article article) {
		jdbcTemplate.update(EDIT, article.getTitle(), article.getTextS(), article.getTextN(), article.getTags(), new Date(), article.getId(), article.getUserId());
	}
	
	public Article findById(Long id){
		return jdbcTemplate.queryForObject(FIND_BY_ID, new RowMapper<Article>(){
			public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
				Article article = new Article();
				article.setId(rs.getLong("id"));
				article.setTitle(rs.getString("title"));
				article.setTextS(rs.getString("text_s"));
				article.setTextN(rs.getString("text_n"));
				article.setUserId(rs.getLong("user_id"));
				article.setTags(rs.getString("tags"));
				article.setAddDt(rs.getTimestamp("add_dt"));
				article.setUpdDt(rs.getTimestamp("upd_dt"));
				article.setStatus(rs.getInt("status"));
				article.setLooks(rs.getInt("looks"));
				article.setLikes(rs.getInt("likes"));
				article.setReplies(rs.getInt("replies"));
				return article;
			}
		}, id);
	}
	
	public ArticleView findViewById(Long id){
		return jdbcTemplate.queryForObject(FIND_VIEW_BY_ID, new RowMapper<ArticleView>(){
			public ArticleView mapRow(ResultSet rs, int rowNum) throws SQLException {
				ArticleView articleView = new ArticleView();
				articleView.setId(rs.getLong("id"));
				articleView.setTitle(rs.getString("title"));
				articleView.setTextS(rs.getString("text_s"));
				articleView.setTextN(rs.getString("text_n"));
				articleView.setUserId(rs.getLong("user_id"));
				articleView.setTags(rs.getString("tags"));
				articleView.setAddDt(rs.getTimestamp("add_dt"));
				articleView.setUpdDt(rs.getTimestamp("upd_dt"));
				articleView.setLooks(rs.getInt("looks"));
				articleView.setLikes(rs.getInt("likes"));
				articleView.setReplies(rs.getInt("replies"));
				articleView.setUsername(rs.getString("username"));
				articleView.setWebsite(rs.getString("website"));
				return articleView;
			}
		}, id);
	}
	
	public void editLooks(Long id, Integer looksPlus){
		jdbcTemplate.update(EDIT_LOOKS, looksPlus, id);
	}
	
	public void editLikes(Long id, Integer likesPlus){
		jdbcTemplate.update(EDIT_LIKES, likesPlus, id);
	}
	
	public void editReplies(Long id, Integer repliesPlus){
		jdbcTemplate.update(EDIT_REPLIES, repliesPlus, id);
	}
	
}
